package com.bj58.data.emailreports.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.FCKTWeekReportCityVo;
import com.bj58.data.emailreports.model.FCKTWeekReportSpreadVo;
import com.bj58.data.emailreports.model.FCKTWeekReportTotalVo;
import com.bj58.data.emailreports.service.FCEmailReportService;
import com.bj58.data.emailreports.utils.CommonDataDeal;
import com.bj58.data.emailreports.utils.DateUtil;

public class FCEmailReportServiceImpl implements FCEmailReportService {
	private CommonDataDeal comDeal = new CommonDataDeal();
	
	
	public List<FCKTWeekReportTotalVo> ktTotalData(String today,
			String lastWeek,String lastMonth, int type) throws Exception {

		StringBuffer sql = new StringBuffer();
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(lastWeek);dateList.add(lastMonth);
		
		for (String searchDate : dateList) {
			String month = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			
			/* totalAmount  cashAmount */
			List<String> cols1 = new ArrayList<String>();
			cols1.add("statDate");
			cols1.add("totalAmount");cols1.add("cashAmount");
			
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"',avg(totalAmount),avg(cashAmount)");
			sql.append(" from (");
			sql.append(" SELECT statDate,sum(totalAmount) totalAmount ,sum(cashAmount)  cashAmount");
			sql.append(" FROM `income_sunmmy_city_cate2`");
			sql.append(" where statDate>=date_sub('"+searchDate+"',interval 6 day) and statDate <='"+searchDate+"'");
			sql.append(" and cate2 ='二手房'");
			if(type == 1){
				sql.append(" and city1 ='北京'");
			}
			sql.append(" group by statDate");
			sql.append(" ) a ");
			//sql.append(" :"+ DBType.ors_r.getValue());
			sql.append(" :"+DBType.db_94_data_app.getValue());
			sqlMap.put(sql.toString(),cols1);		
			
			/* detailPv  detailUv  pvuvRate*/
			List<String> cols2 = new ArrayList<String>();
			cols2.add("statDate");
			cols2.add("detailPv");
			cols2.add("detailUv");
			cols2.add("pvuvRate");
			
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"',avg(vppv),avg(vpuv),avg(vppv)/avg(vpuv) ");
			sql.append(" from (");
			sql.append(" select stat_date,sum(detailpv) as vppv,sum(detailuv) as vpuv");
			sql.append(" from fc_detail_analytics_"+month);
			sql.append(" where cate2='二手房' and city2='' ");
			sql.append(" and stat_date>=subdate('"+searchDate+"',interval 6 day) ");
			sql.append(" and stat_date<='"+searchDate+"'");
			if(type == 1){
				sql.append(" and city1 ='北京'");
			}
			sql.append(" group by stat_date) a");
			//sql.append(" :"+ DBType.stat58.getValue());
			sql.append(" :"+DBType.db_94_data_app.getValue());
			sqlMap.put(sql.toString(),cols2);		
			
			
			/* cusCount*/
			List<String> cols3 = new ArrayList<String>();
			cols3.add("statDate");
			cols3.add("cusCount");
			
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"',count(distinct user_id) ");
			sql.append(" from m_common_order_info ");
			sql.append(" where disp_cate1_name='房产信息' and disp_cate2_names='二手房'");
			sql.append(" and date(service_begin_time)<='"+searchDate+"'" );
			sql.append(" and date(service_end_time)>='"+searchDate+"'"  );
			sql.append(" and order_state in (45,50) ");
			if(type == 1){
				sql.append(" and belong_city_name ='北京'");
			}
			//sql.append(" :"+ DBType.infobright_27_middle.getValue());
			sql.append(" :"+DBType.db_94_data_app.getValue());
			sqlMap.put(sql.toString(),cols3);		
			
			
			/** 北京汇总数据 需计算房源量数据*/
			if(type == 1){
				/* 房源数据 */
				List<String> cols4 = new ArrayList<String>();
				cols4.add("statDate");
				cols4.add("totalHouseInfo");cols4.add("cheapHoustInfo");
				cols4.add("newHouseInfo");cols4.add("newCheapHouseInfo");
				cols4.add("perAvgNewHouseInfo");
				
				sql.delete(0, sql.length()+1);
				sql.append(" select '"+searchDate+"',info_num,info_300,new_info,new_300,new_info/new_infouser");
				sql.append(" from (");
				sql.append(" select count(case when ((states in (1,11) and is_vip='false') or (states in (1,11) and is_vip='true' and is_first=1)) then 1 end) as info_num,");
				sql.append(" count(case when price<=300 and ((states in (1,11) and is_vip='false') or (states in (1,11) and is_vip='true' and is_first=1))then 1 end) as info_300, ");
				sql.append(" count(case when date(created_time)>=subdate('"+searchDate+"',interval 6 day)  and  date(created_time) <='"+searchDate+"'  and date(created_time)=time then 1 end) as new_info, ");
				sql.append(" count(distinct case when date(created_time)>=subdate('"+searchDate+"',interval 6 day) and  date(created_time) <='"+searchDate+"'  and date(created_time)=time then info_userid end) as new_infouser, ");
				sql.append(" count(case when price<=300 and date(created_time)>=subdate('"+searchDate+"',interval 6 day) and  date(created_time) <='"+searchDate+"'  and date(created_time)=time then 1 end) as new_300 ");
				sql.append(" from ershoufang_info_detail ");
				sql.append(" where city1_name='北京' ");
				sql.append(" ) a");
				
				//sql.append(" :"+ DBType.house.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(),cols4);		
			}
			
			/* totalUv*/
			List<String> cols5 = new ArrayList<String>();
			cols5.add("statDate");
			cols5.add("totalUv");
			
			sql.delete(0, sql.length()+1);
			
			sql.append(" select '"+searchDate+"',avg(uv) as uv  ");
			sql.append(" from fc_show_info_"+month);
			sql.append(" where cate_id=12 ");
			sql.append(" and stat_date>=subdate('"+searchDate+"',interval 6 day) ");
			sql.append(" and stat_date<='"+searchDate+"'");
			if(type == 1){
				sql.append(" and local_id=1 ");
			}else if(type == 0){
				sql.append(" and local_id=0 ");
			}
			
			String changDate = "2014-12-01";
			if(DateUtil.strToDate(searchDate, "yyyy-MM-dd").after(DateUtil.strToDate(changDate, "yyyy-MM-dd")) || changDate.equals(searchDate)){
				//sql.append(" :"+ DBType.infobright_58.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
			}else{
				//sql.append(" :"+ DBType.infobright_26_history.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
			}
			
			sqlMap.put(sql.toString(),cols5);		
		}
		
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		
		List<FCKTWeekReportTotalVo> rsList =  comDeal.AssembledDataBySqlMap(sqlMap, FCKTWeekReportTotalVo.class, gbList);
		
		//计算 pvTotalUVRate
		for (FCKTWeekReportTotalVo vo : rsList) {
			if(vo.getTotalUv() > 0 ){
				vo.setPvTotalUVRate((double)vo.getDetailPv()/vo.getTotalUv());
			}
		}
		
		return rsList;
	}


	@Override
	public List<FCKTWeekReportCityVo> ktCityFluxData(String today,
			String lastWeek, String lastMonth) throws Exception {
		StringBuffer sql = new StringBuffer();
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(lastWeek);dateList.add(lastMonth);
		
		for (String searchDate : dateList) {
			String month = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			
			/* detailPv   */
			List<String> cols1 = new ArrayList<String>();
			cols1.add("statDate");cols1.add("city");
			cols1.add("detailPv");
			
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"',city1,avg(vppv)as vppv ");
			sql.append(" from ( ");
			sql.append(" select stat_date,city1,sum(detailpv)as vppv ");
			sql.append(" from fc_detail_analytics_" + month);
			sql.append(" where cate2='二手房'  ");
			sql.append(" and city1 in ('北京', '成都', '重庆', '石家庄', '济南', '青岛', '南京', '杭州', '苏州', '无锡', '合肥', '武汉', '郑州', '长沙', '福州', '厦门', '南宁', '哈尔滨', '长春', '太原', '大连', '沈阳', '上海', '天津', '广州', '深圳', '东莞') ");
			sql.append(" and city2='' and stat_date>=subdate('"+searchDate+"',interval 6 day) and stat_date<='"+searchDate+"'");
			sql.append(" group by stat_date,city1) a ");
			sql.append(" group by city1 ");
			
			//sql.append(" :"+ DBType.stat58.getValue());
			sql.append(" :"+DBType.db_94_data_app.getValue());
			sqlMap.put(sql.toString(),cols1);		
			
			/* uv   jumpOutRate  */
			List<String> cols2 = new ArrayList<String>();
			cols2.add("statDate");cols2.add("city");
			cols2.add("uv");cols2.add("jumpOutRate");
			
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"',city1,avg(uv) as uv,avg(jump_out) /avg(jump_in) as out_percent ");
			sql.append(" from ( ");
			sql.append(" select city1,stat_date,sum(uv) as uv,sum(jump_out) as jump_out,sum(jump_in)as jump_in   ");
			sql.append(" from fc_show_info_" + month);
			sql.append(" WHERE city1 in ('北京', '成都', '重庆', '石家庄', '济南', '青岛', '南京', '杭州', '苏州', '无锡', '合肥', '武汉', '郑州', '长沙', '福州', '厦门', '南宁', '哈尔滨', '长春', '太原', '大连', '沈阳', '上海', '天津', '广州', '深圳', '东莞') ");
			sql.append(" and cate2='二手房' ");
			sql.append(" and city2 is null and cate3 is null  and city1 is not null  ");
			sql.append(" and stat_date>=subdate('"+searchDate+"',interval 6 day) and stat_date<='"+searchDate+"'");
			sql.append(" group by stat_date,city1) a ");
			sql.append(" group by city1 ");
			
			
			String changDate = "2014-12-01";
			if(DateUtil.strToDate(searchDate, "yyyy-MM-dd").after(DateUtil.strToDate(changDate, "yyyy-MM-dd")) || changDate.equals(searchDate)){
				//sql.append(" :"+ DBType.infobright_58.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
			}else{
				//sql.append(" :"+ DBType.infobright_26_history.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
			}
			sqlMap.put(sql.toString(),cols2);		
			

		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("city");
		
		List<FCKTWeekReportCityVo> tempList =  comDeal.AssembledDataBySqlMap(sqlMap, FCKTWeekReportCityVo.class, gbList);
		
		/** 计算  访问深度=  VPPV/UV    */
		for (FCKTWeekReportCityVo vo : tempList) {
			if(vo.getUv() > 0 ){
				vo.setVisitDeep((double) vo.getDetailPv() / vo.getUv() );
			}
		}
		return tempList;
	}


	@Override
	public List<FCKTWeekReportSpreadVo> ktHouseInfoSpreadData(String today,
			String lastWeek, String lastMonth) throws Exception {

		StringBuffer sql = new StringBuffer();
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(lastWeek);dateList.add(lastMonth);
		
		for (String searchDate : dateList) {
			/* newHouseInfo   */
			List<String> cols1 = new ArrayList<String>();
			cols1.add("statDate");cols1.add("type");
			cols1.add("newHouseInfo");
			
			//价格分区间
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"',");
			sql.append(" case when price<60    then '<60' ");
			sql.append(" when price<80    then '[60,80)'");
			sql.append(" when price<100   then '[80,100)'");
			sql.append(" when price<150   then '[100,150)'");
			sql.append(" when price<200   then '[150,200)'");
			sql.append(" when price<300   then '[200,300)' ");
			sql.append(" when price<500   then '[300,500)' ");
			sql.append(" when price<1000  then '[500,1000)' ");
			sql.append(" when price>=1000 then '>=1000'  end as type, ");
			sql.append(" count(distinct info_id)as info_num ");
			sql.append(" from  ershoufang_info_detail ");
			sql.append(" where date(created_time)>=subdate('"+searchDate+"',interval 6 day)  ");
			sql.append(" and date(created_time)<= '"+searchDate+"' ");
			sql.append(" and date(created_time)=time ");
			sql.append(" group by type ");
			
			//sql.append(" :"+ DBType.house.getValue());
			sql.append(" :"+DBType.db_94_data_app.getValue());
			sqlMap.put(sql.toString(),cols1);		
			
			//总计
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"','总计',");
			sql.append(" count(distinct info_id)as info_num ");
			sql.append(" from  ershoufang_info_detail ");
			sql.append(" where date(created_time)>=subdate('"+searchDate+"',interval 6 day)  ");
			sql.append(" and date(created_time)<= '"+searchDate+"' ");
			sql.append(" and date(created_time)=time ");
			
			//sql.append(" :"+ DBType.house.getValue());
			sql.append(" :"+DBType.db_94_data_app.getValue());
			sqlMap.put(sql.toString(),cols1);		
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<FCKTWeekReportSpreadVo> tempList =  comDeal.AssembledDataBySqlMap(sqlMap, FCKTWeekReportSpreadVo.class, gbList);
		Map<String,FCKTWeekReportSpreadVo> allVoMap = new HashMap<String, FCKTWeekReportSpreadVo>();
		for (FCKTWeekReportSpreadVo vo : tempList) {
			if("总计".equals(vo.getType())){
				allVoMap.put(vo.getStatDate(), vo);
			}
		}
		
		/** 加和计算总计VO  并计算占比 */
		for (FCKTWeekReportSpreadVo vo : tempList) {
			if(!"总计".equals(vo.getType())){
				String date = vo.getStatDate();
				FCKTWeekReportSpreadVo totalVo = allVoMap.get(date);
				vo.setNewHouseInfoPro((double) vo.getNewHouseInfo() / totalVo.getNewHouseInfo()	);
			}
		}
		
		return tempList;

	}


	@Override
	public List<FCKTWeekReportSpreadVo> ktClickSpreadData(String today,
			String lastWeek, String lastMonth, int type) throws Exception {

		StringBuffer sql = new StringBuffer();
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(lastWeek);dateList.add(lastMonth);
		
		for (String searchDate : dateList) {
			String month = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			/* newHouseInfo   */
			List<String> cols1 = new ArrayList<String>();
			cols1.add("statDate");cols1.add("type");
			cols1.add("detailPv");cols1.add("clickHouseInfo");
			
			if(type == 0){ //价格
				sql.delete(0, sql.length()+1);
				sql.append(" select '"+searchDate+"',type,avg(vppv) as vppv,avg(info_num) as info_num ");
				sql.append(" from( ");
				sql.append(" select a.time, ");
				sql.append(" case when price<60    then '<60' ");
				sql.append("  when price<80    then '[60,80)'");
				sql.append("  when price<100   then '[80,100)'");
				sql.append("  when price<150   then '[100,150)' ");
				sql.append(" when price<200   then '[150,200)' ");
				sql.append("  when price<300   then '[200,300)'");
				sql.append("  when price<500   then '[300,500)' ");
				sql.append("  when price<1000  then '[500,1000)' ");
				sql.append("  when price>=1000 then '>=1000'end as type,  count(1) as vppv,  count(distinct a.info_id)as info_num ");
				sql.append(" from dbwww58com_house_stats.ershoufang_info_click_"+month+" a ");
				sql.append(" join dbwww58com_house_stats.ershoufang_info_detail b ");
				sql.append(" on a.info_id=b.info_id ");
				sql.append(" where a.time>=subdate('"+searchDate.replace("-", "") +"',interval 6 day) ");
				sql.append(" and a.time <='"+searchDate.replace("-", "")+"' ");
				sql.append(" and price>0 ");
				sql.append(" group by a.time,type) a ");
				sql.append(" group by type ");

				//sql.append(" :"+ DBType.house.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(),cols1);		
				
				//总计
				sql.delete(0, sql.length()+1);
				sql.append(" select '"+searchDate+"','总计',avg(vppv) as vppv,avg(info_num) as info_num ");
				sql.append(" from( ");
				sql.append(" select a.time,count(1) as vppv,  count(distinct a.info_id)as info_num ");
				sql.append(" from dbwww58com_house_stats.ershoufang_info_click_"+month+" a ");
				sql.append(" join dbwww58com_house_stats.ershoufang_info_detail b ");
				sql.append(" on a.info_id=b.info_id ");
				sql.append(" where a.time>=subdate('"+searchDate.replace("-", "")+"',interval 6 day) ");
				sql.append(" and a.time <='"+searchDate.replace("-", "")+"' ");
				sql.append(" group by a.time) a ");

				//sql.append(" :"+ DBType.house.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(),cols1);		
				
			}else if(type == 1){//区域
				sql.delete(0, sql.length()+1);
				sql.append(" select '"+searchDate+"',city2_name,avg(vppv) as vppv,avg(info_num) as info_num ");
				sql.append(" from ( ");
				sql.append(" select b.city2_name,a.time,count(1) as vppv,count(distinct a.info_id)as info_num  ");
				sql.append(" from dbwww58com_house_stats.ershoufang_info_click_"+month+" a ");
				sql.append(" join dbwww58com_house_stats.ershoufang_info_detail b ");
				sql.append(" on a.info_id=b.info_id ");
				sql.append(" where a.time>=subdate('"+searchDate.replace("-", "")+"',interval 6 day) ");
				sql.append(" and a.time <='"+searchDate.replace("-", "")+"' ");
				sql.append(" and b.city2_name<>'暂无' and b.city1_name = '北京' ");
				sql.append(" group by city2_name,a.time) a");
				sql.append(" group by city2_name ");

				//sql.append(" :"+ DBType.house.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(),cols1);		
				
				//总计
				sql.delete(0, sql.length()+1);
				sql.append(" select '"+searchDate+"','总计',avg(vppv) as vppv,avg(info_num) as info_num ");
				sql.append(" from ( ");
				sql.append(" select b.city2_name,a.time,count(1) as vppv,count(distinct a.info_id)as info_num  ");
				sql.append(" from dbwww58com_house_stats.ershoufang_info_click_"+month+" a ");
				sql.append(" join dbwww58com_house_stats.ershoufang_info_detail b ");
				sql.append(" on a.info_id=b.info_id ");
				sql.append(" where a.time>=subdate('"+searchDate.replace("-", "")+"',interval 6 day) ");
				sql.append(" and a.time <='"+searchDate.replace("-", "")+"' ");
				sql.append(" and b.city2_name<>'暂无' and b.city1_name = '北京' ");
				sql.append(" group by a.time) a");

				//sql.append(" :"+ DBType.house.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(),cols1);		
			}else if(type == 2){//客户
				sql.delete(0, sql.length()+1);
				sql.append(" select '"+searchDate+"',user_type,avg(vppv) as vppv,avg(info_num) as info_num ");
				sql.append(" from ( ");
				sql.append(" select case when b.info_userid=-100 then '抓取' else b.user_type end as user_type,a.time,count(1) as vppv,count(distinct a.info_id)as info_num  ");
				sql.append(" from dbwww58com_house_stats.ershoufang_info_click_"+month+" a ");
				sql.append(" join dbwww58com_house_stats.ershoufang_info_detail b ");
				sql.append(" on a.info_id=b.info_id ");
				sql.append(" where a.time>=subdate('"+searchDate.replace("-", "")+"',interval 6 day) ");
				sql.append(" and a.time <='"+searchDate.replace("-", "")+"' ");
				sql.append(" and b.city2_name<>'暂无' and b.city1_name = '北京' ");
				sql.append(" group by user_type,time) a ");
				sql.append(" group by user_type ");

				//sql.append(" :"+ DBType.house.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(),cols1);		
				//总计
				sql.delete(0, sql.length()+1);
				sql.append(" select '"+searchDate+"','总计',avg(vppv) as vppv,avg(info_num) as info_num ");
				sql.append(" from ( ");
				sql.append(" select case when b.info_userid=-100 then '抓取' else b.user_type end as user_type,a.time,count(1) as vppv,count(distinct a.info_id)as info_num  ");
				sql.append(" from dbwww58com_house_stats.ershoufang_info_click_"+month+" a ");
				sql.append(" join dbwww58com_house_stats.ershoufang_info_detail b ");
				sql.append(" on a.info_id=b.info_id ");
				sql.append(" where a.time>=subdate('"+searchDate.replace("-", "")+"',interval 6 day) ");
				sql.append(" and a.time <='"+searchDate.replace("-", "")+"' ");
				sql.append(" and b.city2_name<>'暂无' and b.city1_name = '北京' ");
				sql.append(" group by a.time) a ");
				//sql.append(" :"+ DBType.house.getValue());
				sql.append(" :"+DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(),cols1);		

			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<FCKTWeekReportSpreadVo> tempList =  comDeal.AssembledDataBySqlMap(sqlMap, FCKTWeekReportSpreadVo.class, gbList);
		
		
		Map<String,FCKTWeekReportSpreadVo> allVoMap = new HashMap<String, FCKTWeekReportSpreadVo>();
		for (FCKTWeekReportSpreadVo vo : tempList) {
			if("总计".equals(vo.getType())){
				allVoMap.put(vo.getStatDate(), vo);
			}
		}
		
		/** 加和计算总计VO  并计算详情页PV占比 */
		for (FCKTWeekReportSpreadVo vo : tempList) {
			if(!"总计".equals(vo.getType())){
				String date = vo.getStatDate();
				FCKTWeekReportSpreadVo totalVo = allVoMap.get(date);
				vo.setDetailPvPro((double) vo.getDetailPv() / totalVo.getDetailPv()	);
			}
		}
		
		return tempList;

	}
	
	


	
}
